if exists (select * from dbo.sysobjects where id = object_id(N'MD3.f_GetConstraints'))
drop function MD3.f_GetConstraints
GO

CREATE  function MD3.f_GetConstraints( 
    @SchemaName SYSNAME
   ,@TblName SYSNAME 

)
RETURNS @Table  TABLE   
(    
    TableName	varchar(255) NOT NULL,     
    SchemaName varchar(255) NOT NULL,
    ConstraintName varchar(255) NOT NULL,
    ConstraintExpression varchar(max) NOT NULL,
    ColumnName varchar(255) NULL,
    is_system_named BIT NOT NULL,  
    is_not_trusted	BIT NOT NULL,
    ConstraintType varchar(255) NOT NULL
    
)    
AS  
BEGIN

    DECLARE @FullTblName SYSNAME
	SELECT @FullTblName = @SchemaName + '.' + @TblName

		INSERT INTO @Table
		--column level constraints
        SELECT 
		    OBJECT_NAME(parent_object_id) AS TableName,
		    OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName,
		    OBJECT_NAME(cc.object_id) AS ConstraintName,
		    cc.definition AS ConstraintExpression,
		    cols.name AS ColumnName,
		    cc.is_system_named ,
		    cc.is_not_trusted,
		    'CHECK' AS ConstraintType
	    FROM sys.check_constraints(NOLOCK) cc
		JOIN sys.columns(NOLOCK) cols 
		    ON cc.parent_column_id = cols.column_id
		    AND cc.parent_object_id = cols.object_id
	     WHERE cc.parent_object_id = object_id(@FullTblName)	
	
	    UNION ALL 
		--table level constraints
	     SELECT 
		    OBJECT_NAME(parent_object_id) AS TableName,
		    OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName,
		    OBJECT_NAME(cc.object_id) AS ConstraintName,
		    cc.definition AS CheckConstraintExpression,
		    NULL AS ColumnName,
		    cc.is_system_named ,
		    cc.is_not_trusted,
		    'CHECK' AS ConstraintType
	    FROM sys.check_constraints(NOLOCK) cc
	    WHERE cc.parent_object_id = object_id(@FullTblName)	
			AND cc.parent_column_id = 0
		
	    UNION ALL 
	   --default constraints	       
	   SELECT 
		    OBJECT_NAME(parent_object_id) AS TableName,
		    OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName,
		    OBJECT_NAME(cc.object_id) AS ConstraintName,
		    cc.definition AS CheckConstraintExpression,
		    cols.name AS ColumnName,
		    cc.is_system_named ,
		    0,
		    'DEFAULT' AS ConstraintType
	    FROM sys.default_constraints(NOLOCK) cc
		JOIN sys.columns(NOLOCK) cols 
		    ON cc.parent_column_id = cols.column_id
		    AND cc.parent_object_id = cols.object_id
	     WHERE cc.parent_object_id = object_id(@FullTblName)	
    
  
   
   RETURN
  
END    
GO
